###################################################
################## Section 6
################## Interest rates
################## Plot results 
###################################################
# interest rates are cut below 0% and above 30%

# import libraries
import os
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

path_data = r'P:\ECB business areas\DGR\Staff\papouts\Research\Active\Firm Heterogeneities\Final datasets'
path_charts = r'P:\ECB business areas\DGR\Staff\papouts\Research\Active\Firm Heterogeneities\Final charts\Interest rates\by large firms\final'
if not os.path.isdir(path_charts):
    os.makedirs(path_charts)


# define plotting function               
def plot_weighted_intrst_rt(data, color, order):    
    # data should be a list of dataframes
    # color should be a text
    # order should be a list of lists, where the first is order of all countries, the second is selected countries and the third is the order of type of loans
    sns.set(rc={'figure.figsize':(15,10), 'axes.labelsize':26, 'axes.titlesize':26,  'axes.labelpad':15, 'xtick.labelsize':26, 'ytick.labelsize':26}, style='white')
    
    # plot only by size (no distinction between countries) BUT only selected countries
    plt.figure()
    sns.boxplot(data=data[0].loc[data[0]['CNTRY'].isin(order[1])], x='sz_class', y='W_RT_BYDBTR', width=[0.6], whis=[25, 75], showfliers=False, showmeans=True, medianprops={'color':'red','linewidth':4}, meanprops={'markerfacecolor':'red', 'markeredgecolor':'red', 'markersize':12}, color=color, order=order[3]).set(xlabel='', ylabel='Interest rate', ylim=(0.005, 0.08))
    plt.gca().tick_params(axis='x', pad=25)
    plt.xticks(rotation=10, horizontalalignment='center')
    plt.savefig(path_charts + '\\Section2_intrst_rt_ttlsz_201912.pdf', dpi=600, bbox_inches='tight')
    
    # plot only by instrument type (no distinction between countries) BUT only selected countries
    plt.figure()
    sns.boxplot(data=data[1].loc[data[1]['CNTRY'].isin(order[1])], x='TYP_INSTR_CORR', y='W_RT_BYTYPE', width=[0.6], whis=[25, 75], showfliers=False, showmeans=True, medianprops={'color':'red','linewidth':4}, meanprops={'markerfacecolor':'red', 'markeredgecolor':'red', 'markersize':12}, color=color, order=order[4]).set(xlabel='', ylabel='Interest rate', ylim=(0.005, 0.08))
    plt.xticks(rotation=10, horizontalalignment='center')
    plt.savefig(path_charts + '\\Section2_intrst_rt_ttltype_201912.pdf', dpi=600, bbox_inches='tight')
    
    
    # plot all interest rates by enterprise size    
    # single plots by size for selected countries
    sns.set(rc={'figure.figsize':(15,10), 'axes.labelsize':26, 'axes.titlesize':26,  'axes.labelpad':15, 'xtick.labelsize':26, 'ytick.labelsize':26}, style='white')
    for i, j in enumerate(['Large', 'Medium', 'Small', 'Micro']):
        plt.figure()
        sns.boxplot(data=data[0].loc[data[0]['CNTRY'].isin(order[1])][data[0].loc[data[0]['CNTRY'].isin(order[1])].sz_f == (i+1)], x='CNTRY', y='W_RT_BYDBTR', width=[0.6], whis=[25, 75], showfliers=False, showmeans=True, medianprops={'color':'red','linewidth':4}, meanprops={'markerfacecolor':'red', 'markeredgecolor':'red', 'markersize':12}, color=color, order=order[1]).set(xlabel='', ylabel='Interest rate')
        plt.savefig(path_charts + '\\Section6_intrst_rt_bysz_' + j + '_201912.pdf', dpi=600, bbox_inches='tight')

    
    # plot all interest rates by type of credit    
    # single plots by credit type for selected countries
    sns.set(rc={'figure.figsize':(15,10), 'axes.labelsize':26, 'axes.titlesize':26,  'axes.labelpad':15, 'xtick.labelsize':26, 'ytick.labelsize':26}, style='white')
    for i,j in enumerate(order[2]):
        plt.figure()
        sns.boxplot(data=data[1].loc[data[1]['CNTRY'].isin(order[1])][data[1].loc[data[1]['CNTRY'].isin(order[1])].TYP_INSTR_CORR.str.contains(order[2][i]) == True], x='CNTRY', y='W_RT_BYTYPE', width=[0.6], whis=[25, 75], showfliers=False, showmeans=True, medianprops={'color':'red','linewidth':4}, meanprops={'markerfacecolor':'red', 'markeredgecolor':'red', 'markersize':12}, color=color, order=order[1]).set(xlabel='', ylabel='Interest rate')
        plt.savefig(path_charts + '\\Appendix_intrst_rt_bytype_' + j + '_201912.pdf', dpi=600, bbox_inches='tight')

# load data
AC_final_bytype = pd.read_stata(path_data + '\instr_lvl_dt_f.dta')
AC_final_bytype.rename(columns={'cntry_dbtr':'CNTRY', 'typ_instr_corr':'TYP_INSTR_CORR', 'wir_byloantype':'W_RT_BYTYPE'}, inplace=True)
AC_final_bytype.drop(AC_final_bytype[AC_final_bytype.nace_code == ''].index, inplace=True)
AC_final_bytype.drop(AC_final_bytype[AC_final_bytype.W_RT_BYTYPE.isna() == True].index, inplace=True)
print(AC_final_bytype.isna().sum())

# construct debtor level dataset
AC_final_bytype['wir_ona'] = AC_final_bytype['ona_intrate_c_ttl'] * AC_final_bytype['W_RT_BYTYPE']
wir_ona = AC_final_bytype.groupby(['CNTRY','dbtr_id'])['wir_ona'].sum().reset_index().rename(columns={'wir_ona':'weighted_sum'})
ona_dbtr = AC_final_bytype.groupby(['CNTRY','dbtr_id'])['ona_intrate_c_ttl'].sum().reset_index().rename(columns={'ona_intrate_c_ttl':'ona_sum'})
wir_ona = wir_ona.merge(ona_dbtr, how='left', on=['CNTRY','dbtr_id'])
wir_ona['W_RT_BYDBTR'] = wir_ona['weighted_sum']/wir_ona['ona_sum']
AC_final_bytype = AC_final_bytype.merge(wir_ona,how='left',on=['CNTRY','dbtr_id'])
# debtor level
AC_final_bydbtr = AC_final_bytype[['CNTRY', 'dbtr_id', 'sz_f', 'nace_code','W_RT_BYDBTR']].drop_duplicates()
print(AC_final_bydbtr.isna().sum())

# map size class
map_size = {1:'Large', 2:'Medium', 3:'Small', 4:'Micro'}
AC_final_bydbtr['sz_class'] = AC_final_bydbtr['sz_f'].map(map_size)
print(AC_final_bydbtr[['sz_f','sz_class']].drop_duplicates())

# plot
# order countries by mean value of overall for instrument type charts
# order countries by mean value of large firms for size charts
#order_cntry = ['AT','BE','CY','DE','EE','ES','FI','FR','GR','IE','IT','LT','LU','LV','MT','NL','PT','SI','SK']
sel_countries = ['AT','BE','DE','ES','FI','FR','GR','IE','IT','NL','PT']
order_all_cntry = AC_final_bydbtr.groupby(['CNTRY'])['W_RT_BYDBTR'].mean().sort_values(ascending=False).index
order_slctd_cntry = order_all_cntry[order_all_cntry.isin(['AT','BE','DE','ES','FI','FR','GR','IE','IT','NL','PT'])]
order_all = AC_final_bydbtr[AC_final_bydbtr.sz_f == 1].groupby(['CNTRY'])['W_RT_BYDBTR'].mean().sort_values(ascending=False).index
# order selected countries by large firms
order_slctd = order_all[order_all.isin(sel_countries)]

loantype_order = ['Loans', 'Credit lines', 'Revolving credit', 'Finance leases', 'Trade receivables']
order_sz_slctd = AC_final_bydbtr[(AC_final_bydbtr.CNTRY.isin(sel_countries) == True)].groupby(['sz_class'])['W_RT_BYDBTR'].mean().sort_values(ascending=False).index
order_loan_slctd = AC_final_bytype[(AC_final_bytype.CNTRY.isin(sel_countries) == True)].groupby(['TYP_INSTR_CORR'])['W_RT_BYTYPE'].mean().sort_values(ascending=False).index
   
plot_weighted_intrst_rt(data=[AC_final_bydbtr, AC_final_bytype], color='#7ccaa5', order=[order_all, order_slctd, loantype_order, order_sz_slctd, order_loan_slctd, order_all_cntry, order_slctd_cntry])
                   
